ALTER function fn_load_line_items_for_splitting(@salesorder_id int)

returns @fn_result Table (SalesOrder_No varchar(50), LineItem_Id int, COOL_Line_Item_No int,COOL_PO_Line_Item_No int, Product_ID int, 
COOL_Qty int, COOL_Unit_Cost float, COOL_Amount float, COOL_Requested_Date datetime, 
COOL_Sugg_Schedule_Date_With_Substitutables datetime, COOL_Sugg_Schedule_Date_Without_Substitutables datetime, 
COOL_Schedule_Date datetime, COOL_Revised_Date datetime, COOL_Is_Cancel int, COOL_Status varchar(50), COOL_Taxable int, COOL_Description varchar(200), Total_Shipped_Issued_Qty int, Customer_PartNumber varchar(50), COOL_Final_Status varchar(50), COOL_Is_Expedite int,
COOL_CUSTOM_PRICE_FIELD1 float, COOL_CUSTOM_TEXT_FIELD1 varchar(1000)) 

as
begin

declare @salesorder_no varchar(50), @lineitem_Id int, @lineitem_no int, @po_line_item_no int, @product_id int, @qty int, @unit_cost float, @amount float, @req_date datetime,
	@sugg_sch_date_with_sub datetime, @sugg_sch_date_without_sub datetime, @sch_date datetime, @revised_date datetime, @is_cancel int, @status varchar(50), @taxable int, @description varchar(200), @total_shipped_issued_qty int
declare @customer_partnumber varchar(50)
declare @customer_id int
declare @cool_final_status varchar(50)

declare @qty_shipped int
declare @temp_cslit_status varchar(50)

-- Prabhanjan (3 Apr 2004)
declare @end_user varchar(50)
declare @end_user_id varchar(50)
declare @is_expedite int
DECLARE @COOL_CUSTOM_PRICE_FIELD1 float
DECLARE @COOL_CUSTOM_TEXT_FIELD1 varchar(1000)

declare line_item_cursor cursor for
	select  distinct COOL.LineItem_ID ,COOL.COOL_Line_Item_No,COOL_PO_Line_Item_No, Product_ID, 
		COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, 
		COOL_Sugg_Schedule_Date_With_Substitutables, COOL_Sugg_Schedule_Date_Without_Substitutables, 
		COOL_Schedule_Date, COOL_Revised_Date, COOL_Is_Cancel, COOL_Status, COOL_Taxable, COOL_Description, COOL_Is_Expedite, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 
		from Customer_Open_Order_LineItem COOL where COOL.SalesOrder_Id = @salesorder_id 

			
	open line_item_cursor
	fetch next from line_item_cursor into @lineitem_id, @lineitem_no, @po_line_item_no, @product_id, @qty, @unit_cost, @amount, @req_date, @sugg_sch_date_with_sub, @sugg_sch_date_without_sub, @sch_date, @revised_date, @is_cancel, @status, @taxable, @description, @is_expedite, @customer_partnumber, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1 
			
	select @salesorder_no = SalesOrder_No , @customer_id = Customer_Id, @end_user = SO_EndUser from Sales_Order where SalesOrder_ID = @salesorder_id

	while @@fetch_status = 0
	begin
		select @total_shipped_issued_qty = sum(CSLIT_Qty_Shipped) from Customer_Shipping_Line_Item_Table CSLIT where LineItem_Id = @lineitem_id and CSLIT_STATUS <> 'CANCELLED'
		if(@total_shipped_issued_qty is null)
		begin
			set @total_shipped_issued_qty = 0
		end
	 	IF ( @status = 'APPROVED' )
		BEGIN
			SET @cool_final_status = 'APPROVED'
		  
			SELECT  @qty_shipped =COALESCE (SUM(CSLIT_QTY_SHIPPED), null), @temp_cslit_status = CSLIT_STATUS 
					FROM   customer_shipping_line_item_table WITH (NOLOCK)where LineItem_ID =  @lineitem_id 
					and CSLIT_STATUS <> 'CANCELLED'
					GROUP BY LineItem_ID,CSLIT_STATUS 

			IF @qty_shipped is not null and @temp_cslit_status = 'SHIPPED'
			BEGIN
				IF  (@qty-@qty_shipped ) > 0 
				BEGIN
					SET @cool_final_status = 'PARTIALLY SHIPPED'
				END
				ELSE IF (@qty-@qty_shipped ) = 0 
				BEGIN
					SET @cool_final_status = 'SHIPPED'
				END
			END
			ELSE IF @temp_cslit_status = 'ISSUED'
			BEGIN
				SET @cool_final_status = 'ISSUED'
			END
		END
		ELSE IF ( @status = 'SHIPPED' )
		BEGIN
			SET @cool_final_status = 'SHIPPED'
		END
		ELSE
		BEGIN
			SET @cool_final_status = 'HOLD'
		END	
		insert into @fn_result values(@salesorder_no, @lineitem_id, @lineitem_no, @po_line_item_no, @product_id, @qty, @unit_cost, @amount, @req_date, @sugg_sch_date_with_sub, @sugg_sch_date_without_sub, @sch_date, @revised_date, @is_cancel, @status, @taxable, @description, @total_shipped_issued_qty, @customer_partnumber, @cool_final_status, @is_expedite, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1 )

		set @total_shipped_issued_qty = null
		set @cool_final_status = ''
		set @temp_cslit_status = null
 		set @qty_shipped = null
		
		fetch next from line_item_cursor into @lineitem_id, @lineitem_no, @po_line_item_no, @product_id, @qty, @unit_cost, @amount, @req_date, @sugg_sch_date_with_sub, @sugg_sch_date_without_sub, @sch_date, @revised_date, @is_cancel, @status, @taxable, @description, @is_expedite, @customer_partnumber, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1 
	end
	close line_item_cursor
	deallocate line_item_cursor
return
end	
